import Warning from 'components/Markdown/Warning'

export const meta = {
  title: "Connect Existing Database (Introspection)",
  position: 210,
}

## Overview

When connecting Prisma to an existing database that already has a database schema and/or contains some data, it can be tedious to manually write the datamodel from scratch while ensuring it matches the structure of the already existing data.

To automate this process, you can use the [`prisma introspect`](xcv9#prisma-introspect) command from the [Prisma CLI](alx4) to generate the [datamodel](knul) based on the actual structure of the existing data.

The generated SDL serves as a foundation for your Prisma service, but you can easily make modifications afterwards as you see fit. Some common modifications include hiding a table from the GraphQL API or giving a column to a different name.

<Warning>

Currently database introspection only works with Postgres. Additionally there is a set of documented [known limitations](https://github.com/prisma/prisma/issues/2377).

</Warning>


## Introspecting a Postgres database

Postgres uses the following model to organize databases internally:

![](https://i.imgur.com/0YJBgbW.png)

**IMPORTANT**: When introspecting a Postgres database, you're actually introspecting a _schema_ and not a _database_ according to the illustrated model!

There are two ways you can use the CLI to introspect a Postgres schema:

- Using the interactive `prisma init` wizard
- Using the dedicated `prisma introspect` command

In both cases, you need to provide the _connection details_ for the running Postgres database. This includes the following:

- **Host**: The host of your Postgres server, e.g. `localhost`.
- **Port**: The port where your Postgres server listens, e.g. `5432`.
- **User & Password**: The credentials for your Postgres server.
- **Name of existing _database_**: The name of the Postgres _database_ (according to the illustrated model from above).
- **Use SSL (Yes/No)**: If your database connection is using SSL, you need to select `Yes`, otherwise `No`.
- **Name of existing _schema_**: The name of the Postgres _schema_ (according to the illustrated model from above), e.g. `public`.

### Using the `prisma init` wizard

During the interactive `prisma init` flow you can choose to connect to an existing database with data. The CLI will ask for database connection details (as mentioned above) and verify that it can establish a successful connection.

If the connection details are valid, the CLI will introspect the database and show you a summary.

![](https://i.imgur.com/cNIeeJf.png)

When `prisma init` terminates, the CLI has created the following files for you which you can now use to _deploy_ a new Prisma service:

- `datamodel.prisma`: Contains the datamodel (in SDL) that was generated based on your existing database.
- `docker-compose.yml`: The Docker Compose file containing the configuration of your Prisma server, including details about how to connect to your database
- prisma.yml: The root configuration file for your service

To be able to query your Postgres database using GraphQL you now need to _deploy_ the service and open a GraphQL Playground:

```bash
prisma deploy
prisma playground
```

### Using `prisma introspect`

`prisma introspect` works in a similar way as the `prisma init` wizard in that you need to provide the database connection information.

While `prisma init` wizard generates an entire _service configuration_, `prisma introspect` only generates the datamodel file:

- `datamodel-[TIMESTAMP].graghpql`: The timestamp component allows you to use the introspect command for an existing Prisma service without overriding your existing datamodel.

Before deploying your service with the generated datamodel, you should ensure that the `migrations` property (of the `PRISMA_CONFIG` environment variable) in the Docker Compose file that specifies your Prisma server configuration is set to `false`:

```yml
PRISMA_CONFIG: |
  port: 4466
  databases:
    default:
      connector: postgres
      migrations: false # be sure this is set to false
      host: localhost
      port: 5432
      user: postgres
      password: postgres
      database: postgres
      schema: public
```

Setting `migrations` to `false` ensures that Prisma only updates the GraphQL API of the server based on the datamodel, but it does _not_ perform migrations of your underlying Postgres database.

## Relations in the generated datamodel

Depending on your approach to model a relation between tables in your SQL database, the relation in the generated datamodel might look different. Here is the overview of how Prisma interprets relations specified in SQL:

| Relation in SQL | Relation generated by Prisma (SDL) |
| --- | --- |
|Inline relation column | One-to-Many |
| Relation table | Many-to-Many |
| Relation table with extra column | Dedicated type to express relationship |

### Inline relation column

A common way to represents relationships in a SQL database is via a _foreign key_ constraint:

```sql
CREATE TABLE product (
  id           serial PRIMARY KEY UNIQUE
, description  text NOT NULL
);

CREATE TABLE bill (
  id         serial PRIMARY KEY UNIQUE
, notes      text NOT NULL
, product_id int REFERENCES product (id) ON UPDATE CASCADE
);
```

In this case, `bill` uses a foreign key to reference the `id` of the `product` table and therefore create a _relation_ between the two tables. Based on this table structure, Prisma generates two SDL models, `Product` and `Bill`, with a _bidirectional_ relation (via the `product` and `bills` fields):

```graphql
type Bill @pgTable(name: "bill") {
  notes: String!
  id: Int! @unique
  product: Product @pgRelation(column: "product_id")
}

type Product @pgTable(name: "product") {
  description: String!
  id: Int! @unique
  bills: [Bill!]!
}
```

Here is how Prisma generates the names for the generated fields:

- `type Bill`
  - `notes: String` is named after the `notes` collumn on the `bill` table
  - `id: Int!` is named after the `id` column on the `bill` table
  - `product: Product` is named after the `product_id` column on the `bill` table; Prisma strips off the following suffixes of such foreign key field: `_id`, `ID` and `Id`
- `type Product`
  - `description: String` is named after the `description` collumn on the `product` table
  - `id: Int!` is named after the `id` column on the `product` table
  - `bills: [Bill!]!` is named after the _plural_ version of the `bill` table

### Relation tables

Another common approach to represent relations in a SQL database is via a dedicated _relation table_ which "connects" the two related tables:

```sql
CREATE TABLE product (
  id         serial PRIMARY KEY UNIQUE
, product    text NOT NULL
);

CREATE TABLE bill (
  id       serial PRIMARY KEY UNIQUE
, bill     text NOT NULL
);

CREATE TABLE bill_product (
  bill_id    int REFERENCES bill (id) ON UPDATE CASCADE ON DELETE CASCADE
, product_id int REFERENCES product (id) ON UPDATE CASCADE
);
```

In this case, the relation table is called `bill_prouct` and simply … simply contains two columns which both are foreign keys to the `id` column of the `bill` and `product` tables.

### Relation with extra columns

Sometimes it's helpful to enrich a relation table with meta-information about the relation itself:

```sql
CREATE TABLE product (
  id         serial PRIMARY KEY UNIQUE
, product    text NOT NULL
);

CREATE TABLE bill (
  id       serial PRIMARY KEY UNIQUE
, bill     text NOT NULL
);

CREATE TABLE bill_product (
  bill_id    int REFERENCES bill (id) ON UPDATE CASCADE ON DELETE CASCADE
, product_id int REFERENCES product (id) ON UPDATE CASCADE
, some_other_column text NOT NULL
);
```

If a relation table contains such extra information, Prisma treats the relation table as a _dedicated type_:

```graphql
type Bill @pgTable(name: "bill") {
  bill: String!
  id: Int! @unique
  bill_products: [Bill_product]
}

type Bill_product @pgTable(name: "bill_product") {
  bill: Bill @pgRelation(column: "bill_id")
  product: Product @pgRelation(column: "product_id")
  some_other_column: String!
}

type Product @pgTable(name: "product") {
  id: Int! @unique
  product: String!
  bill_products: [Bill_product]
}
```

 This way you have the full flexibility to set and read the extra column(s) using normal queries and nested mutations.
